ALTER PROCEDURE [dbo].[spe_smsfetch_ucis_wjcjbz]
AS

/****************************************************
  Program_Name: spe_grab_ucis_wjcjbz
  Program_Desc: UCIS月中催缴-拼接短信
  Commentary:上海过河兵
  Revision: V1.0
  Author: Tao
  Date: 2019-01-04
A00012	UCIS月中催缴
***************************************************/

begin
	declare @t_id int
	declare @t_yhbh varchar(20)			--用户编号
	declare @t_ysdz varchar(200)		--用水地址
	declare @t_tqje decimal(13,2)	--拖欠总金额（水费，污水费，垃圾处理费）  
	declare	@t_sjhm varchar(30)		--手机号码
	declare @t_tqsf decimal(13,2)	--水费
	declare @t_tqpsf decimal(13,2)	--污水费
	declare @t_tqljf decimal(13,2)	--垃圾处理费
	declare @nf	int	
	declare @yf	int
	declare @yhbh	varchar(20)	
	declare @tqje	decimal(13, 2)	
	declare @yhbm	varchar(4)
	declare @yhzh	varchar(50)	
	declare @lxdh	varchar(50)	
	declare @ysdz	varchar(100)	
	declare @sjhm	varchar(30)	
	declare @tqlx	varchar(6)	
	declare @tqrq1 datetime	
	declare @tqrq2 datetime	
	declare @tqsf	decimal(13, 2)	
	declare @tqpsf decimal(13, 2)
	declare @tqljf decimal(13, 2)
	declare @id	int
	declare @tel_1 varchar(20)
	declare @tel_2 varchar(20)
	declare @tel1status int
	declare @tel2status int 
	declare @smsstatus int
	declare @t_count int
	declare @tel1 varchar(50)
	declare @tel2 varchar(50)
	declare @n int
	declare @i int 
	declare @cj_guid varchar(36)
	declare @t_usercodecount int,@t_tqje_sum decimal(13,2),@t_tqsf_sum decimal(13,2),@t_tqpsf_sum decimal(13,2),@t_tqljf_sum decimal(13,2)
	declare @t_smsinfo varchar(2000),@t_smsguid varchar(36),@t_expectedstartdate datetime
	declare @sms_guid varchar(36)
	declare @g_status int
	declare @doc_count int
	declare @cj_count int
	declare @t_amount int
	declare @i_smsnum int,@t_INTERVAL_LENGTH int
	declare @c_batch varchar(2)
	declare @t_itemid bigint
	declare @t_deptcode varchar(10), @isHBNum int
	DECLARE @tguid VARCHAR(36)
	DECLARE @sname VARCHAR(255)
	DECLARE @sms_content varchar(2000)
	DECLARE @sm_phone varchar(20)
	DECLARE @deptcode varchar(20)
  DECLARE @sms_content_time datetime
	--获取是否有需要处理的短信拼接任务
	Select @g_Status = Count(*) From Ucis_Sms_Grab Where Type_Id = 'A00012' and status1 is null

	if @g_status>0
	begin

		--查询待处理任务的唯一编号
		select top 1 @sms_guid=sms_guid from ucis_sms_grab where TYPE_ID='A00012' and status1 is null

		if @sms_guid!=''
		begin
			--更新监控信息表
			update ucis_sms_monitoring set doc_starttime=GETDATE(),cj_starttime=GETDATE() where SMS_GUID=@sms_guid

			set @i_smsnum=0

			--定义任务处理游标
			declare cur_sjhm_a00012 cursor for 
				Select Sjhm,
							 Count(*),
							 Sum(Case
										 When Smsmix = '1' Then
											1
										 Else
											0
									 End)
					From Ucis_Wj_Cjbz
				 Where Sms_Guid = @Sms_Guid
					 And Sjhm Is Not Null
					 And Sjhm != '' and  isgsjt= '1'
					 And Len(Sjhm) = 11
				 Group By Sjhm

			--处理发送记录
			open cur_sjhm_a00012 fetch next from cur_sjhm_a00012 into @t_sjhm, @t_usercodecount, @isHBNum
			while @@fetch_status=0
			begin
				begin transaction
				--同一个手机号码多个任务是合并发送，规则：超过5条且合并标识全部为1
				if @t_usercodecount>5 and @isHBNum >= @t_usercodecount
				begin
					select @t_tqje_sum=sum(tqje),@t_tqsf_sum=sum(tqsf),@t_tqpsf_sum=sum(tqpsf),@t_tqljf_sum=sum(tqljf) from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'

					set @t_smsguid=newid()
					select @t_smsinfo=content from smsmodel where ID='100003' and type='A00002'
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_usercodecount)
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',cast(@t_tqsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_tqpsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_tqljf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqje_sum as varchar(20)))

					--写历史记录表
					begin
						--将要合并的当个任务插入历史表
						insert into ucis_wj_cjbz_his(rec_guid,nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,doc_guid,sms_guid,smsmix,reducedMoney,ISGSJT)
						select newid(),nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,@t_smsguid,@sms_guid,smsmix,reducedMoney,ISGSJT from ucis_wj_cjbz  
						where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1' 

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					begin
						declare user_Cursortest1 cursor for select id,lxdh,lxdh2 from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid  and  isgsjt= '1'--定义游标
						open user_Cursortest1 --打开游标
						fetch next from user_Cursortest1 into @id,@tel1,@tel2 
						while @@fetch_status=0  --执行成功 
						begin 
							set @cj_guid=newid()

							--获取信息
							select @nf=nf,@yf=yf,@yhbh=yhbh,@sjhm=sjhm,@lxdh=lxdh,@tqje=tqje,@yhbm=yhbm,@yhzh=yhzh,@ysdz=ysdz,@tqlx=tqlx,@tqrq1=tqrq1,@tqrq2=tqrq2,@tqsf=tqsf,@tqpsf=tqpsf,@tqljf=tqljf,@id=id,@t_itemid=itemid,@t_deptcode=deptcode from ucis_wj_cjbz where id=@id AND sms_guid = @sms_guid and  isgsjt= '1'

							--插入CJ_Comprehensive_UCIS
							insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,lxdh,tel_1,tel_2,doc_guid,sms_comment,type_id,tqje,yhbm,yhzh,ysdz,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,id,CJ_GUID,sms_guid,itemid,deptcode)values 
							(@nf,@yf,@yhbh,@sjhm,@lxdh,@tel1,@tel2,@t_smsguid,@t_smsinfo,'A00012',@tqje,@yhbm,@yhzh,@ysdz,@tqlx,@tqrq1,@tqrq2,@tqsf,@tqpsf,@tqljf,@id,@cj_guid,@sms_guid,@t_itemid,@t_deptcode)
								
							fetch next from user_Cursortest1 into @id,@tel1,@tel2
						end;
						Close user_Cursortest1 --关闭游标
						deallocate user_Cursortest1
					END

					begin
						--写发送表			
						insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,TRIALCOUNT,TRIALCOUNTLIMIT,sms_guid)
						values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),null,'A00012',0,3,@sms_guid)

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
						set @i_smsnum=@i_smsnum+1
					end
					
					begin
					--清除发送记录
						delete from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
				end
				else if (@t_usercodecount>1 and  @t_usercodecount<=5) or (@t_usercodecount>5 and @isHBNum < @t_usercodecount)
				begin
					declare cur_sjhmin5 cursor for select id from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'  
					open cur_sjhmin5
					fetch next from cur_sjhmin5 into @t_id
					while @@fetch_status=0
					begin
						begin transaction
						select @t_yhbh=yhbh,@t_ysdz=ysdz,@t_tqje=tqje,@t_tqsf=tqsf,@t_tqpsf=tqpsf,@t_tqljf=tqljf from ucis_wj_cjbz where sjhm=@t_sjhm and id=@t_id and sms_guid=@sms_guid  and  isgsjt= '1'
						set @t_smsguid=newid()
						select @t_smsinfo=content  from smsmodel where ID='100004' and type='A00002'
						set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
						set @t_ysdz=REPLACE(@t_ysdz,' ','')
						set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
						set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_tqsf as varchar(20)))
						set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_tqpsf as varchar(20)))
						set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqljf as varchar(20)))
						set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_tqje as varchar(20)))
						--写历史记录表
						begin
							insert into ucis_wj_cjbz_his(rec_guid,nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,doc_guid,sms_guid,smsmix,reducedMoney,ISGSJT)
							select newid(),nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,@t_smsguid,@sms_guid,smsmix,reducedMoney,ISGSJT from ucis_wj_cjbz  
							where id=@t_id and sms_guid=@sms_guid and  isgsjt= '1'
							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						begin
							declare user_Cursortest1 cursor for select id,lxdh,lxdh2 from ucis_wj_cjbz where id=@t_id and sms_guid=@sms_guid   and  isgsjt= '1'--定义游标
							open user_Cursortest1 --打开游标
							fetch next from user_Cursortest1 into @id,@tel1,@tel2 
							while @@fetch_status=0  --执行成功 
							begin 
								set @cj_guid=newid()
								begin
									select @nf=nf,@yf=yf,@yhbh=yhbh,@sjhm=sjhm,@lxdh=lxdh,@tqje=tqje,@yhbm=yhbm,@yhzh=yhzh,@ysdz=ysdz,@tqlx=tqlx,@tqrq1=tqrq1,@tqrq2=tqrq2,@tqsf=tqsf,@tqpsf=tqpsf,@tqljf=tqljf,@id=id,@t_itemid=itemid,@t_deptcode=deptcode from ucis_wj_cjbz where id=@id AND sms_guid = @sms_guid   and  isgsjt= '1'
									insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,lxdh,tel_1,tel_2,doc_guid,sms_comment,type_id,tqje,yhbm,yhzh,ysdz,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,id,CJ_GUID,sms_guid,itemid,deptcode)values 
									(@nf,@yf,@yhbh,@sjhm,@lxdh,@tel1,@tel2,@t_smsguid,@t_smsinfo,'A00012',@tqje,@yhbm,@yhzh,@ysdz,@tqlx,@tqrq1,@tqrq2,@tqsf,@tqpsf,@tqljf,@id,@cj_guid,@sms_guid,@t_itemid,@t_deptcode)
								end
								
								fetch next from user_Cursortest1 into @id,@tel1,@tel2
							end;
							Close user_Cursortest1 --关闭游标
							deallocate user_Cursortest1
						END			
						--写发送表
						begin
							insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,STRINGFIELD2,TRIALCOUNT,TRIALCOUNTLIMIT,sms_guid)
							values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),null,'A00012',@t_yhbh,0,3,@sms_guid)
							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
							set @i_smsnum=@i_smsnum+1
						end
						--清除发送记录
						begin
							delete from ucis_wj_cjbz where sjhm=@t_sjhm and id=@t_id and sms_guid=@sms_guid   and  isgsjt= '1'
							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						nextpoint2:
						commit transaction
						fetch next from cur_sjhmin5 into @t_id
					end
					close cur_sjhmin5
					deallocate cur_sjhmin5
				end
				else if @t_usercodecount=1
				begin
					select @t_yhbh=yhbh,@t_ysdz=ysdz,@t_tqje=tqje,@t_tqsf=tqsf,@t_tqpsf=tqpsf,@t_tqljf=tqljf from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt= '1'
					
					set @t_smsguid=newid()
					select @t_smsinfo=content from smsmodel where ID='100004' and type='A00002'
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_tqsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_tqpsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_tqje as varchar(20)))
						
					--写历史记录表
					begin
						insert into ucis_wj_cjbz_his(rec_guid,nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,doc_guid,sms_guid,smsmix,reducedMoney,ISGSJT)
						select newid(),nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,@t_smsguid,@sms_guid,smsmix,reducedMoney,ISGSJT from ucis_wj_cjbz 
						where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt= '1'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
						
					BEGIN
						declare user_Cursortest1 cursor for select id,lxdh,lxdh2 from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt= '1'--where len(lxdh)>11 --定义游标
						open user_Cursortest1 --打开游标
						fetch next from user_Cursortest1 into @id,@tel1,@tel2 
						while @@fetch_status=0  --执行成功 
						begin 
							set @cj_guid=newid()
							begin
								select @nf=nf,@yf=yf,@yhbh=yhbh,@sjhm=sjhm,@lxdh=lxdh,@tqje=tqje,@yhbm=yhbm,@yhzh=yhzh,@ysdz=ysdz,@tqlx=tqlx,@tqrq1=tqrq1,@tqrq2=tqrq2,@tqsf=tqsf,@tqpsf=tqpsf,@tqljf=tqljf,@id=id,@t_itemid=itemid,@t_deptcode=deptcode from ucis_wj_cjbz where id=@id AND sms_guid = @sms_guid   and  isgsjt= '1'
								insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,lxdh,tel_1,tel_2,doc_guid,sms_comment,type_id,tqje,yhbm,yhzh,ysdz,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,id,CJ_GUID,sms_guid,itemid,deptcode)values 
								(@nf,@yf,@yhbh,@sjhm,@lxdh,@tel1,@tel2,@t_smsguid,@t_smsinfo,'A00012',@tqje,@yhbm,@yhzh,@ysdz,@tqlx,@tqrq1,@tqrq2,@tqsf,@tqpsf,@tqljf,@id,@cj_guid,@sms_guid,@t_itemid,@t_deptcode)
							end
														
							fetch next from user_Cursortest1 into @id,@tel1,@tel2 
						end;
						Close user_Cursortest1 --关闭游标
						deallocate user_Cursortest1
					END			
					--写发送表
					begin
						insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,STRINGFIELD2,TRIALCOUNT,TRIALCOUNTLIMIT,sms_guid)
						values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),null,'A00012',@t_yhbh,0,3,@sms_guid)
						if @@error<>0
							begin
								rollback transaction
								goto nextpoint1
							end
						set @i_smsnum=@i_smsnum+1
					end
					--清除发送记录
					begin
						delete from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt= '1'
						if @@error<>0
							begin
								rollback transaction
								goto nextpoint1
							end
					end
				end;
						
				nextpoint1:
				commit transaction
				fetch next from cur_sjhm_a00012 into @t_sjhm,@t_usercodecount,@isHBNum
			end;
			close cur_sjhm_a00012
			deallocate cur_sjhm_a00012
			---------------------------------------------------------isgsjt=0------------------------------------------------------------------
			
			set @i_smsnum=0

			--定义任务处理游标
			declare cur_sjhm_a00012 cursor for 
				Select Sjhm,
							 Count(*),
							 Sum(Case
										 When Smsmix = '1' Then
											1
										 Else
											0
									 End)
					From Ucis_Wj_Cjbz
				 Where Sms_Guid = @Sms_Guid
					 And Sjhm Is Not Null
					 And Sjhm != '' and  isgsjt=0
					 And Len(Sjhm) = 11
				 Group By Sjhm

			--处理发送记录
			open cur_sjhm_a00012 fetch next from cur_sjhm_a00012 into @t_sjhm, @t_usercodecount, @isHBNum
			while @@fetch_status=0
			begin
				begin transaction
				--同一个手机号码多个任务是合并发送，规则：超过5条且合并标识全部为1
				if @t_usercodecount>5 and @isHBNum >= @t_usercodecount
				begin
					select @t_tqje_sum=sum(tqje),@t_tqsf_sum=sum(tqsf),@t_tqpsf_sum=sum(tqpsf),@t_tqljf_sum=sum(tqljf) from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt=0

					set @t_smsguid=newid()
					select @t_smsinfo=content from smsmodel where ID='000003' and type='A00002'
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_usercodecount)
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',cast(@t_tqsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_tqpsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_tqljf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqje_sum as varchar(20)))

					--写历史记录表
					begin
						--将要合并的当个任务插入历史表
						insert into ucis_wj_cjbz_his(rec_guid,nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,doc_guid,sms_guid,smsmix,reducedMoney,ISGSJT)
						select newid(),nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,@t_smsguid,@sms_guid,smsmix,reducedMoney,ISGSJT from ucis_wj_cjbz  
						where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt=0 

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					begin
						declare user_Cursortest1 cursor for select id,lxdh,lxdh2 from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid  and  isgsjt=0--定义游标
						open user_Cursortest1 --打开游标
						fetch next from user_Cursortest1 into @id,@tel1,@tel2 
						while @@fetch_status=0  --执行成功 
						begin 
							set @cj_guid=newid()

							--获取信息
							select @nf=nf,@yf=yf,@yhbh=yhbh,@sjhm=sjhm,@lxdh=lxdh,@tqje=tqje,@yhbm=yhbm,@yhzh=yhzh,@ysdz=ysdz,@tqlx=tqlx,@tqrq1=tqrq1,@tqrq2=tqrq2,@tqsf=tqsf,@tqpsf=tqpsf,@tqljf=tqljf,@id=id,@t_itemid=itemid,@t_deptcode=deptcode from ucis_wj_cjbz where id=@id AND sms_guid = @sms_guid and  isgsjt=0

							--插入CJ_Comprehensive_UCIS
							insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,lxdh,tel_1,tel_2,doc_guid,sms_comment,type_id,tqje,yhbm,yhzh,ysdz,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,id,CJ_GUID,sms_guid,itemid,deptcode)values 
							(@nf,@yf,@yhbh,@sjhm,@lxdh,@tel1,@tel2,@t_smsguid,@t_smsinfo,'A00012',@tqje,@yhbm,@yhzh,@ysdz,@tqlx,@tqrq1,@tqrq2,@tqsf,@tqpsf,@tqljf,@id,@cj_guid,@sms_guid,@t_itemid,@t_deptcode)
								
							fetch next from user_Cursortest1 into @id,@tel1,@tel2
						end;
						Close user_Cursortest1 --关闭游标
						deallocate user_Cursortest1
					END

					begin
						--写发送表			
						insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,TRIALCOUNT,TRIALCOUNTLIMIT,sms_guid)
						values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),null,'A00012',0,3,@sms_guid)

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
						set @i_smsnum=@i_smsnum+1
					end
					
					begin
					--清除发送记录
						delete from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt=0
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
				end
				else if (@t_usercodecount>1 and  @t_usercodecount<=5) or (@t_usercodecount>5 and @isHBNum < @t_usercodecount)
				begin
					declare cur_sjhmin5 cursor for select id from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt=0  
					open cur_sjhmin5
					fetch next from cur_sjhmin5 into @t_id
					while @@fetch_status=0
					begin
						begin transaction
						select @t_yhbh=yhbh,@t_ysdz=ysdz,@t_tqje=tqje,@t_tqsf=tqsf,@t_tqpsf=tqpsf,@t_tqljf=tqljf from ucis_wj_cjbz where sjhm=@t_sjhm and id=@t_id and sms_guid=@sms_guid  and  isgsjt=0
						set @t_smsguid=newid()
						select @t_smsinfo=content  from smsmodel where ID='000004' and type='A00002'
						set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
						set @t_ysdz=REPLACE(@t_ysdz,' ','')
						set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
						set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_tqsf as varchar(20)))
						set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_tqpsf as varchar(20)))
						set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqljf as varchar(20)))
						set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_tqje as varchar(20)))
						--写历史记录表
						begin
							insert into ucis_wj_cjbz_his(rec_guid,nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,doc_guid,sms_guid,smsmix,reducedMoney,ISGSJT)
							select newid(),nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,@t_smsguid,@sms_guid,smsmix,reducedMoney,ISGSJT from ucis_wj_cjbz  
							where id=@t_id and sms_guid=@sms_guid and  isgsjt=0
							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						begin
							declare user_Cursortest1 cursor for select id,lxdh,lxdh2 from ucis_wj_cjbz where id=@t_id and sms_guid=@sms_guid   and  isgsjt=0--定义游标
							open user_Cursortest1 --打开游标
							fetch next from user_Cursortest1 into @id,@tel1,@tel2 
							while @@fetch_status=0  --执行成功 
							begin 
								set @cj_guid=newid()
								begin
									select @nf=nf,@yf=yf,@yhbh=yhbh,@sjhm=sjhm,@lxdh=lxdh,@tqje=tqje,@yhbm=yhbm,@yhzh=yhzh,@ysdz=ysdz,@tqlx=tqlx,@tqrq1=tqrq1,@tqrq2=tqrq2,@tqsf=tqsf,@tqpsf=tqpsf,@tqljf=tqljf,@id=id,@t_itemid=itemid,@t_deptcode=deptcode from ucis_wj_cjbz where id=@id AND sms_guid = @sms_guid   and  isgsjt=0
									insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,lxdh,tel_1,tel_2,doc_guid,sms_comment,type_id,tqje,yhbm,yhzh,ysdz,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,id,CJ_GUID,sms_guid,itemid,deptcode)values 
									(@nf,@yf,@yhbh,@sjhm,@lxdh,@tel1,@tel2,@t_smsguid,@t_smsinfo,'A00012',@tqje,@yhbm,@yhzh,@ysdz,@tqlx,@tqrq1,@tqrq2,@tqsf,@tqpsf,@tqljf,@id,@cj_guid,@sms_guid,@t_itemid,@t_deptcode)
								end
								
								fetch next from user_Cursortest1 into @id,@tel1,@tel2
							end;
							Close user_Cursortest1 --关闭游标
							deallocate user_Cursortest1
						END			
						--写发送表
						begin
							insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,STRINGFIELD2,TRIALCOUNT,TRIALCOUNTLIMIT,sms_guid)
							values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),null,'A00012',@t_yhbh,0,3,@sms_guid)
							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
							set @i_smsnum=@i_smsnum+1
						end
						--清除发送记录
						begin
							delete from ucis_wj_cjbz where sjhm=@t_sjhm and id=@t_id and sms_guid=@sms_guid   and  isgsjt=0
							if @@error<>0
							begin
								rollback transaction
								goto nextpoint3
							end
						end
						nextpoint3:
						commit transaction
						fetch next from cur_sjhmin5 into @t_id
					end
					close cur_sjhmin5
					deallocate cur_sjhmin5
				end
				else if @t_usercodecount=1
				begin
					select @t_yhbh=yhbh,@t_ysdz=ysdz,@t_tqje=tqje,@t_tqsf=tqsf,@t_tqpsf=tqpsf,@t_tqljf=tqljf from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt=0
					
					set @t_smsguid=newid()
					select @t_smsinfo=content from smsmodel where ID='000004' and type='A00002'
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_tqsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_tqpsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_tqje as varchar(20)))
						
					--写历史记录表
					begin
						insert into ucis_wj_cjbz_his(rec_guid,nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,doc_guid,sms_guid,smsmix,reducedMoney,ISGSJT)
						select newid(),nf,yf,yhbh,tqje,yhbm,yhzh,lxdh,ysdz,sjhm,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,lxdh2,sfznj,psfznj,ljfznj,itemid,deptcode,ID,@t_smsguid,@sms_guid,smsmix,reducedMoney,ISGSJT from ucis_wj_cjbz 
						where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt=0
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
						
					BEGIN
						declare user_Cursortest1 cursor for select id,lxdh,lxdh2 from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt=0--where len(lxdh)>11 --定义游标
						open user_Cursortest1 --打开游标
						fetch next from user_Cursortest1 into @id,@tel1,@tel2 
						while @@fetch_status=0  --执行成功 
						begin 
							set @cj_guid=newid()
							begin
								select @nf=nf,@yf=yf,@yhbh=yhbh,@sjhm=sjhm,@lxdh=lxdh,@tqje=tqje,@yhbm=yhbm,@yhzh=yhzh,@ysdz=ysdz,@tqlx=tqlx,@tqrq1=tqrq1,@tqrq2=tqrq2,@tqsf=tqsf,@tqpsf=tqpsf,@tqljf=tqljf,@id=id,@t_itemid=itemid,@t_deptcode=deptcode from ucis_wj_cjbz where id=@id AND sms_guid = @sms_guid   and  isgsjt=0
								insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,lxdh,tel_1,tel_2,doc_guid,sms_comment,type_id,tqje,yhbm,yhzh,ysdz,tqlx,tqrq1,tqrq2,tqsf,tqpsf,tqljf,id,CJ_GUID,sms_guid,itemid,deptcode)values 
								(@nf,@yf,@yhbh,@sjhm,@lxdh,@tel1,@tel2,@t_smsguid,@t_smsinfo,'A00012',@tqje,@yhbm,@yhzh,@ysdz,@tqlx,@tqrq1,@tqrq2,@tqsf,@tqpsf,@tqljf,@id,@cj_guid,@sms_guid,@t_itemid,@t_deptcode)
							end
														
							fetch next from user_Cursortest1 into @id,@tel1,@tel2 
						end;
						Close user_Cursortest1 --关闭游标
						deallocate user_Cursortest1
					END			
					--写发送表
					begin
						insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,STRINGFIELD2,TRIALCOUNT,TRIALCOUNTLIMIT,sms_guid)
						values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),null,'A00012',@t_yhbh,0,3,@sms_guid)
						if @@error<>0
							begin
								rollback transaction
								goto nextpoint1
							end
						set @i_smsnum=@i_smsnum+1
					end
					--清除发送记录
					begin
						delete from ucis_wj_cjbz where sjhm=@t_sjhm and sms_guid=@sms_guid   and  isgsjt=0
						if @@error<>0
							begin
								rollback transaction
								goto nextpoint4
							end
					end
				end;
						
				nextpoint4:
				commit transaction
				fetch next from cur_sjhm_a00012 into @t_sjhm,@t_usercodecount,@isHBNum
			end;
			close cur_sjhm_a00012
			deallocate cur_sjhm_a00012

			---------------------------------------------------------isgsjt=0------------------------------------------------------------------

			--更新生成完成状态
			begin
				update DOCUMENTOUT_UCIS set reportstatus='FINISH' where SMS_GUID=@sms_guid
        update ucis_smssendplan set STATUS=2 where rec_guid=@sms_guid
				PRINT @sms_guid
			end		
			
			begin
				select @doc_count=count(*) from DOCUMENTOUT_UCIS where sms_guid=@sms_guid 
				select @cj_count=COUNT(*) from CJ_Comprehensive_UCIS where sms_guid=@sms_guid
				update ucis_sms_monitoring set doc_sum=@doc_count,cj_sum=@cj_count,doc_endtime=GETDATE(),cj_endtime=GETDATE(),smsfetch_status=1 where SMS_GUID=@sms_guid
			end
			begin
			 update ucis_sms_grab set status1='1'  where type_id='A00012' and status1 is null  AND SMS_GUID = @sms_guid
			update CJ_Comprehensive_UCIS set createdate = GETDATE() where sms_guid=@sms_guid
			end
				BEGIN
					select top 1 @tguid = REC_GUID,@sname = sms_name from ucis_smssendplan where rec_guid = @sms_guid
					INSERT INTO message_operation_his VALUES (@tguid,@sname,'job', GETDATE(), '内容拼接',NEWID());
				END
				--审核通知短信
				begin
					select @sms_content=sms_name+'，短信拼接已完成，时间为'+CONVERT(varchar(30),GETDATE(),120)+'，请及时前往计划页面配置发送时间、分批设置。【深圳水务集团】',@deptcode = deptcode  from ucis_smssendplan 
					where type_id='A00012' and rec_guid=@sms_guid
					if @sms_content!='' or @sms_content is not null
					begin
						declare sms_audits_A00012 cursor for select phone from sms_audits where area = @deptcode
						open sms_audits_A00012
						fetch next from sms_audits_A00012 into @sm_phone
						while @@fetch_status=0
						begin

             SELECT @sms_content_time=CASE WHEN GETDATE() BETWEEN 
             Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+' '+'09:00:00'
             AND 
             Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+' '+'23:59:59'
             THEN GETDATE() ELSE Datename(year,GetDate())+'-'+Datename
             (month,GetDate())+'-'+Datename(day,GetDate())+' '+'09:00:00' END

							--begin
							--insert into ITSV.szwaterDB.DBO.gxsendlist (content,sendtime,loginname,mobile,typework,bh,status,isbirthday,depid,schtime)
							--values(@sms_content,@sms_content_time,'linzm',@sm_phone,'UCIS月中催缴','SZWG','0','0',1,@sms_content_time)
							--end
							fetch next from sms_audits_A00012 into @sm_phone
						end
						close sms_audits_A00012
						deallocate sms_audits_A00012
					end
				end
		end
	end
end